#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlRecord>
#include <QSqlField>
#include <QDebug>
#include "DataHelper.h"

bool DataHelper::newChart(const SPatient &patient)
{
	this->connect();

	bool bResult(false);
	int iChart(0);
	QSqlQuery query("select chart from patient order by id desc limit 1");

	if(query.exec())
	{
		if(query.next())
		{
			QString chart = query.record().field(0).value().toString();
			bool bOk;

			iChart = chart.toInt(&bOk);

			if(!bOk) iChart = 0;
			else iChart++;
		}

		query.finish();

		this->printQueryInfo(query);

		query.prepare("insert into patient (id, chart, name, sex, birth, idNumber, phone) values(null, ?, ?, ?, ?, ?, ?)");
		query.addBindValue(QString("%1").arg(iChart, 10, 10, QChar('0')));
		query.addBindValue(patient.m_strName);
		query.addBindValue(patient.m_iSex);
		query.addBindValue(patient.m_dateBirth.toString(strDateFormat));
		query.addBindValue(patient.m_strIDNumber);
		query.addBindValue(patient.m_strPhone);

		if(query.exec()) bResult = true;
		else {}

		this->printQueryInfo(query);
	}
	else {}

	this->disconnect(bResult);

	return bResult;
}

QList<SPatient> DataHelper::searchChart(const SPatient &patient)
{
	this->connect();

	QStringList whereList;
	QList<QVariant> whereValue;

	if(patient.m_iID != -1)
	{
		whereList.append("id = ?");
		whereValue.append(patient.m_iID);
	}

	if(!patient.m_strChart.isEmpty())
	{
		whereList.append("chart = ?");
		whereValue.append(patient.m_strChart);
	}

	if(!patient.m_strName.isEmpty())
	{
		whereList.append("name = ?");
		whereValue.append(patient.m_strName);
	}

	if(!patient.m_strIDNumber.isEmpty())
	{
		whereList.append("idnumber = ?");
		whereValue.append(patient.m_strIDNumber);
	}

	if(patient.m_dateLastCapture.isValid())
	{
		whereList.append("lastCapture like ?");
		whereValue.append(patient.m_dateLastCapture.toString(strDateFormat).append("%"));
	}

	//select 쿼리 준비
	QString strQuery("select id, chart, name, sex, birth, idNumber, phone, lastCapture from patient");

	//where 조건절 생성
	if(whereList.count() > 0)
	{
		strQuery.append(" where ");
		strQuery.append(whereList.join(" and "));
	}

	//chart 역순으로 정렬
	strQuery.append(" order by id desc");

	QSqlQuery query;

	query.prepare(strQuery);

	//where 조건절 값 입력
	if(whereValue.count() > 0)
	{
		foreach (const QVariant &var, whereValue)
		{
			query.addBindValue(var);
		}
	}

	QList<SPatient> listPatient;

	if(query.exec())
	{
		while (query.next())
		{
			SPatient patient;

			patient.m_iID = query.record().field(0).value().toInt();
			patient.m_strChart = query.record().field(1).value().toString();
			patient.m_strName = query.record().field(2).value().toString();
			patient.m_iSex = query.record().field(3).value().toInt();
			patient.m_dateBirth = QDate::fromString(query.record().field(4).value().toString(), strDateFormat);
			patient.m_strIDNumber = query.record().field(5).value().toString();
			patient.m_strPhone = query.record().field(6).value().toString();
			patient.m_dateLastCapture = QDateTime::fromString(query.record().field(7).value().toString(), strDateTimeFormat);
			qDebug() << "[capture date]" << query.record().field(7).value()<< query.record().field(7).value().toString() << QDateTime::fromString(query.record().field(7).value().toString(), strDateTimeFormat);

			listPatient.append(patient);
		}
	}

	this->printQueryInfo(query);

	this->disconnect();

	return listPatient;
}

SPatient DataHelper::getChart(int iID)
{
	this->connect();

	SPatient patient;

	patient.m_iID = iID;

	QList<SPatient> listPatient = this->searchChart(patient);

	SPatient result;

	if(listPatient.count() == 1)
	{
		result = listPatient.first();
	}

	this->disconnect();

	return result;
}

QList<SCapturePerDate> DataHelper::searchCapturePerDay(int iID)
{
	this->connect();

	QSqlQuery query;

	query.prepare("select capturedate, position from capture where patient = ? order by capturedate asc, position asc");
	query.addBindValue(iID);

	QList<SCapturePerDate> listCapture;
	QMap<QDate, QList<int> > mapCapture;

	if(query.exec())
	{
		while (query.next())
		{
			QDate date = QDateTime::fromString(query.record().field(0).value().toString(), strDateTimeFormat).date();
			int iPos = query.record().field(1).value().toInt();

			mapCapture[date] = QList<int>(mapCapture.value(date)) << iPos;
		}

		if(mapCapture.count() > 0)
		{
			QMapIterator<QDate, QList<int> > im(mapCapture);
			while (im.hasNext())
			{
				im.next();

				SCapturePerDate capture;

				capture.m_iPatientID = iID;
				capture.m_timeCapture = im.key();
				capture.m_listPosition = QList<int>(im.value());
				listCapture.append(capture);
			}
		}
	}

	this->disconnect();

	return listCapture;
}

QList<SCapture> DataHelper::searchCapture(int iPatientID, QDate dateCapture)
{
	this->connect();

	QString strQuery("select position, fullpath from capture where patient = ? and capturedate like ? order by id desc");
	QSqlQuery query;

	query.prepare(strQuery);
	query.addBindValue(iPatientID);
	query.addBindValue(dateCapture.toString(strDateFormat).append("%"));

	QList<SCapture> listCapture;

	if(query.exec())
	{
		while(query.next())
		{
			SCapture capture;

			capture.m_iPosition = query.record().field(0).value().toInt();
			capture.m_strFilepath = query.record().field(1).value().toString();

			listCapture << capture;
		}
	}

	this->printQueryInfo(query);

	this->disconnect();

	return listCapture;
}

bool DataHelper::newCapture(const SCapture &capture)
{
	this->connect();

	QString strQuery("insert into capture values (null, ?, ?, ?, ?)");
	QSqlQuery query;

	query.prepare(strQuery);
	query.addBindValue(capture.m_strFilepath);
	query.addBindValue(capture.m_dtCapture.toString(strDateTimeFormat));
	query.addBindValue(capture.m_iPosition);
	query.addBindValue(capture.m_iPatientID);

	bool bResult = query.exec();

	this->printQueryInfo(query);

	query.clear();

	QString strQueryUpdate("update patient set lastCapture = ? where id = ?");

	query.prepare(strQueryUpdate);
	query.addBindValue(capture.m_dtCapture.toString(strDateTimeFormat));
	query.addBindValue(capture.m_iPatientID);
	bResult &= query.exec();

	this->printQueryInfo(query);

	this->disconnect(bResult);

	return bResult;
}

bool DataHelper::existPatient(const SPatient &patient)
{
	this->connect();

	QString strQuery("select id from patient where name = ? and idNumber like ?");
	QSqlQuery query;

	query.prepare(strQuery);
	query.addBindValue(patient.m_strName);
	query.addBindValue(patient.m_strIDNumber + "%");
	bool bResult = query.exec();

	if(bResult) bResult = query.next();

	this->printQueryInfo(query);

	return bResult;
}

void DataHelper::printQueryInfo(const QSqlQuery &query) const
{
	qDebug() << "query -" << query.lastQuery();
	qDebug() << "query error -" << query.lastError();
	qDebug("+bound values");

	QMapIterator<QString, QVariant> it(query.boundValues());

	while (it.hasNext())
	{
		it.next();
		qDebug() << it.key() << it.value();
	}

	qDebug("-bound values");
}

void DataHelper::connect()
{
	QString strDatabasePath = QFileInfo(QDir::current(), strDBFilename).absoluteFilePath();

	if(QFile::exists(strDatabasePath))
	{
		if(!QSqlDatabase::contains())
		{
			QSqlDatabase::addDatabase("QSQLITE").setDatabaseName(strDatabasePath);
		}

		QSqlDatabase db = QSqlDatabase::database();

		if(!db.isOpen()) db.open();

		QSqlDatabase::database().transaction();
	}
}

void DataHelper::disconnect(bool bCommit)
{
	if(bCommit) QSqlDatabase::database().commit();
	else QSqlDatabase::database().rollback();
}
